package dao.visitor;

import domain.Visitors;
import util.DButils;

import javax.swing.plaf.nimbus.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class VisitorDaoImpl implements VisitorDao {
    /**
     * 新建访客信息
     * @param room_id,community_id,name,phone,idcard,carid,photo_img,reason,cometime,validtime
     * @return int
     * @author 夏天
     * @date 2020/7/23 15:07
     */
    @Override
    public int CreatVisitor(int room_id,int community_id,String name,String phone,String idcard,String carid,String photo_img,String reason,String cometime,String validtime) throws Exception{
        String sql = "insert into visitors (Room_id,Community_id,Name,Phone,Idcard,Carid,Photo_img,Reason,Cometime,Validtime) values("+
                room_id+","+community_id+",'"+name+"','"+phone+"','"+idcard+"','"+carid+"','"+photo_img+"','"+reason+"','"+cometime+"','"+validtime+"')";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i =st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 通过room_id查询访客列表
     * @param room_id
     * @return ArrayList
     * @author 夏天
     * @date 2020/7/23 15:18
     */
    @Override
    public ArrayList<Visitors> getVisitors(int room_id)throws Exception{
        String sql = "select Visitors_id,Name,Cometime,Validtime from visitors where Room_id="+room_id;
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ArrayList<Visitors> list =new ArrayList<>();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            Visitors visitors = new Visitors();
            visitors.setVisitors_id(rs.getInt("Visitors_id"));
            visitors.setName(rs.getString("Name"));
            visitors.setCometime(rs.getString("Cometime"));
            visitors.setValidtime(rs.getString("Validtime"));
            list.add(visitors);
        }
        DButils.close(rs,st,con);
        return list;
    }

    /**
     * 删除访客信息
     * @param visitors_id
     * @return int
     * @author 夏天
     * @date 2020/7/23 15:31
     */
    @Override
    public  int DelectVisitors(int visitors_id)throws  Exception{
        String sql = "delete from visitors where Visitors_id="+visitors_id;
        Connection con = DButils.getConn();
        Statement st =con.createStatement();
        int i =st.executeUpdate(sql);
        DButils.close(st,con);
        return  i;
    }

    /**
     * 查看单条数据的详细信息
     * @param visitors_id
     * @return Visitors
     * @author 夏天
     * @date 2020/7/23 15:37
     */
    public  Visitors seeInfo(int visitors_id)throws Exception{
        String sql = "select * from visitors where Visitors_id = "+visitors_id;
        Visitors visitors = new Visitors();
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            visitors.setVisitors_id(rs.getInt("Visitors_id"));
            visitors.setRoom_id(rs.getInt("Room_id"));
            visitors.setCommunity_id(rs.getInt("Community_id"));
            visitors.setName(rs.getString("Name"));
            visitors.setPhone(rs.getString("Phone"));
            visitors.setIdcard(rs.getString("Idcard"));
            visitors.setCarid(rs.getString("Carid"));
            visitors.setPhoto_img(rs.getString("Photo_img"));
            visitors.setReason(rs.getString("Reason"));
            visitors.setCometime(rs.getString("Cometime"));
            visitors.setValidtime(rs.getString("Validtime"));
        }
        return visitors;
    }

    /**
     * 更新访客信息
     * @param visitors_id,phone,idcard,carid,photo_img,reason,cometime,validtime
     * @retrun int
     * @author 夏天
     * @date 2020/7/23 16:09
     */
    public  int UpdateVisitor(int visitors_id,String phone,String idcard,String carid,String photo_img,String reason,String cometime,String validtime) throws  Exception{
        String sql = "update visitors set Phone='"+phone+"',Idcard='"+idcard+"',Carid='"+carid+"',Photo_img='"+photo_img+"',Reason='"+reason+"',Cometime='"+cometime+"',Validtime='"+validtime+"' where Visitors_id="+visitors_id;
        if(photo_img == null)
            sql = "update visitors set Phone='"+phone+"',Idcard='"+idcard+"',Carid='"+carid+"',Reason='"+reason+"',Cometime='"+cometime+"',Validtime='"+validtime+"' where Visitors_id="+visitors_id;
        Connection con = DButils.getConn();
        Statement st =con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }
}
